<?php


namespace yy\helper;


use PhpOffice\PhpSpreadsheet\IOFactory;
use yy\exception\Error;

/**
 * excel 处理类
 * Class Excel
 * @package yy\helper
 */
class Excel
{
    private $styleArray = [
        // 边框
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            ],
        ],
        // 水平居中
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
        ],
    ];


    public static function init(){
        return new self();
    }

    /**
     * 读取excel返回数组
     * @param $filename
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function readToArray($filename)
    {
        $fileExtendName = substr(strrchr($filename, '.'), 1);
        if (!in_array($fileExtendName , ['xls','xlsx']))throw new Error( 400 ,'必须为excel表格，且必须为xls或xlsx格式！' );
        $reader = IOFactory::createReader(ucfirst($fileExtendName));
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load(app()->getRootPath() . $filename);

        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

        return $sheetData;
    }

    /**
     * 导出excel
     * @param $data 导出数据
     * @param $downname 文件名称
     * @param string $filename 保存到磁盘上的路径，空就是输出到浏览器
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportXlsx($data ,$downname,$filename = '')
    {
        $downname = iconv("UTF-8","gbk//TRANSLIT" , $downname) .time() . '.xlsx';
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $spreadsheet->getActiveSheet()->fromArray($data);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->setPreCalculateFormulas(false);
        if (!empty($filename)){
            $writer->save($filename);
            return;
        }
        header('Content-Description: File Transfer');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $downname);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
    }

    /**
     * 这个函数用不了
     * @param $data
     * @param string $filename
     */
    public function exportCsv($data ,$filename = '')
    {
        // halt($data);
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $spreadsheet->getActiveSheet()->fromArray($data);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
        $writer->setPreCalculateFormulas(false);
        header('Content-Description: File Transfer');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=test.csv');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');

        $fp = fopen('php://output', 'a');//打开output流
        mb_convert_variables('GBK', 'UTF-8', $columns);
        fputcsv($fp, $columns);//将数据格式化为csv格式并写入到output流中
        $dataNum = count( $data );
        $perSize = 1000;//每次导出的条数
        $pages = ceil($dataNum / $perSize);

        for ($i = 1; $i <= $pages; $i++) {
            foreach ($data as $item) {
                mb_convert_variables('GBK', 'UTF-8', $item);
                fputcsv($fp, $item);
            }
            //刷新输出缓冲到浏览器
            ob_flush();
            flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。
        }
        fclose($fp);
        exit();
    }

    /**
     * excel 读取时间数字转时间
     * @param $number
     * @param bool $timestamp
     * @return false|float|int|string
     */
    public function numberToDate($number , $timestamp = true)
    {
        $second = 24 * 3600 * ($number - 25569);
        if ($timestamp)return $second;
        return date("Y-m-d" , $second);

    }


    /**
     * 导出xlsx 带格式
     * @param $data array 数据二维数组
     * @param $downname string 下载文件名
     * @param array $merge 合并单元格 ['A1:B2' , 'A4:B6']
     * @param array $columnWidths 设置列宽 ['A' => 19 , 'B' => 14]
     * @param string $filename 保存本地文件路径 如果有此字段则保存到本地
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportXlsxHasStyle($data ,$downname,$merge = [] ,$columnWidths = [] ,$filename = '')
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($downname);
        $worksheet->fromArray($data);
        // 样式
        if ($merge){
            foreach ($merge as $item) {
                $worksheet->mergeCells($item);
                $worksheet->getStyle($item)
                    ->applyFromArray($this->styleArray);
            }

        }

        if ($columnWidths){
            foreach ($columnWidths as $column => $width) {
                $worksheet->getColumnDimension($column)
                    ->setWidth($width);
            }

        }
        $end = end($data);
        $column = $this->addition(count($end));
        $worksheet->calculateColumnWidths();
        $worksheet->getStyle('A1:' . $column . count($data))
            ->applyFromArray($this->styleArray);
        $worksheet->getStyle('A1:' . $column . count($data))->getAlignment()->setWrapText(true);
        // end
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->setPreCalculateFormulas(false);
        if (!empty($filename)){
            $writer->save($filename);
            return;
        }
        $downname = iconv("UTF-8","gbk//TRANSLIT" , $downname) .time() . '.xlsx';
        header('Content-Description: File Transfer');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $downname);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
    }


    public function exportXlsxHasStyleBatch($array ,$downname,$merge = [] ,$columnWidths = [] ,$filename = '')
    {
        // dd($array);
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        foreach ($array as $index => $item) {
            $spreadsheet->createSheet($index);
            $worksheet = $spreadsheet->setActiveSheetIndex($index);
            $worksheet->setTitle($item['title'] ?: 'Sheet' . $index);
            $data = $item['data'];
            $worksheet->fromArray($data);


            // 样式
            if ($merge) {
                $worksheet->mergeCells(join(":", $merge));
            }

            if ($columnWidths) {
                foreach ($columnWidths as $column => $width) {
                    $worksheet->getColumnDimension($column)
                        ->setWidth($width);
                }

            }
            $end    = end($data);
            $column = chr(65 + count($end) - 1);
            $worksheet->calculateColumnWidths();
            $worksheet->getStyle('A1:' . $column . count($data))
                ->applyFromArray($this->styleArray);
            // end
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->setPreCalculateFormulas(false);
        if (!empty($filename)){
            $writer->save($filename);
            return;
        }
        $downname = iconv("UTF-8","gbk//TRANSLIT" , $downname) .time() . '.xlsx';
        header('Content-Description: File Transfer');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $downname);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
    }

    public function addition($num ,$f = 'A')
    {
        for ($i = 0 ; $i < $num ; $i++)$f++;
        return $f;

    }



}